package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zretc.dao.StatisticsDao;
import com.zretc.util.DBUtil;

public class StatisticsDaoImpl implements StatisticsDao {

	@Override
	public List<Map<String, Object>> findEveryRegionTotalIncome() {
		List<Map<String, Object>> list = new ArrayList<>();
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append("select");
		sql.append(" home_address,");
		sql.append(" sum(roominfo.room_price) as income");
		sql.append(" from homeinfo inner join roominfo");
		sql.append(" on homeinfo.home_id = roominfo.home_id");
		sql.append(" where roominfo.room_id");
		sql.append(" in(select room_id from orderinfo)");
		sql.append(" group by home_address");
		
		rs = DBUtil.doQuery(sql.toString());
		
		try {
			while (rs.next()) {
				String homeAddress = rs.getString("home_address");
				Integer income = rs.getInt("income");
				// 将当前列的数据存储到一个map中
				Map<String, Object> map = new HashMap<>();
				map.put("homeAddress", homeAddress);
				map.put("income", income);
				// 将map存储到集合list中
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Map<String, Object>> findEveryStyleTotalIncome() {
		List<Map<String, Object>> list = new ArrayList<>();
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append("select");
		sql.append(" style_type,");
		sql.append(" sum(roominfo.room_price) as income");
		sql.append(" from homeinfo inner join roominfo");
		sql.append(" on homeinfo.home_id=roominfo.home_id");
		sql.append(" inner join orderinfo on orderinfo.room_id=roominfo.room_id");
		sql.append(" inner join styleinfo on homeinfo.style_id=styleinfo.style_id");
		sql.append(" where roominfo.room_id");
		sql.append(" in(select room_id from orderinfo)");
		sql.append(" group by style_type");
		
		rs = DBUtil.doQuery(sql.toString());
		
		try {
			while (rs.next()) {
				String styleType = rs.getString("style_type");
				Integer income = rs.getInt("income");
				
				// 将当前列的数据存储到一个map中
				Map<String, Object> map = new HashMap<>();
				map.put("styleType", styleType);
				map.put("income", income);
				// 将map存储到集合list中
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}
